namespace Webbness.Generator.CodeDom

import System
import System.Text
import System.IO
import Neo.MetaModel
import ConnectionFactory
import System.Data.SqlClient
import System.Data
import System.Collections
import System.Collections.Specialized
import Webbness.Generator.CodeDom.Sql
import Neo.Generator.CodeGen
import Neo.MetaModel.Reader
import Neo.Generator.Core
import NVelocity.Runtime.Resource
import NVelocity.Runtime.Resource.Loader
import NVelocity.App
import NVelocity.Runtime
import Commons.Collections

class DatabaseDiff:
	
	Template as string:
		get:
			return _template
		set:
			_template = value
	
	TemplatesFolder as string:
		get:
			return _templatesFolder
		set:
			_templatesFolder = value
	
	[property(Model)]
	_model as Model
	
	ProjectName as string:
		get:
			return _projectName
		set:
			_projectName = value
			
	OutputFolder as string:
		get:
			return _outputRoot
		set:
			_outputRoot = value
	
	ForceOverwrite as bool:
		get:
			return _forceOverwrite
		set:
			_forceOverwrite = value
	
	CreateFolders as bool:
		get:
			return _createFolders
		set:
			_createFolders = value
	
	CreatedFiles as ArrayList:
		get:
			return _createdFiles
	
	[property(Properties)]
	_properties as StringDictionary
	
	ConnectionString as String:
		get:
			return "data source=${_properties['DBServer']};initial catalog=${_properties['Database']};user id=${_properties['DBUserName']};password=${_properties['DBPassword']};persist security info=true"
	
	Revision as String:
		get:
			if String.IsNullOrEmpty(_properties['Revision']):
				return String.Empty
			return "_${_properties['Revision']}"
	
	Output as String:
		get:
			return _sb.ToString()
	
	_sb as StringBuilder
	_converter as IDBTypeConverter
	_conn as IDbConnection
	_databaseModel as DatabaseModelContext
	protected _template as string
	protected _templatesFolder as string
	protected _outputRoot as string
	protected _forceOverwrite as bool
	protected _createFolders as bool
	protected _createdFiles as ArrayList
	protected _projectName as string
	
	def constructor():
		_properties = StringDictionary()
		_sb = StringBuilder()
		_converter = SQLDbTypeConverter()
		_createdFiles = ArrayList()
		
	def Run():
		_databaseModel = DatabaseModelContext(_model)
		connFactory = DefaultConnectionFactory(typeof(SqlConnection), ConnectionString)
		tables = DataTable("Tables")
		
		using _conn = connFactory.CreateConnection():
			try:
				command = _conn.CreateCommand()
				command.CommandText = "select table_name as Name from INFORMATION_SCHEMA.Tables where TABLE_TYPE = 'BASE TABLE'"
				_conn.Open()
				tables.Load(command.ExecuteReader(CommandBehavior.Default))
				ValidateTables(tables)
				for entity as Entity in _model.Entities:
					cols = DataTable("Columns")
					colCommand = _conn.CreateCommand()
					colCommand.CommandText = "SELECT *, COLUMNPROPERTY(OBJECT_ID('${entity.TableName}'), COLUMN_NAME, 'IsIdentity') AS IsIdentity FROM Information_Schema.columns where table_name = '${entity.TableName}'"
					cols.Load(colCommand.ExecuteReader(CommandBehavior.Default))
					ValidateColumns(cols, entity)
					ValidateRelations(entity)
			
			except e as System.Exception:
				_sb.AppendLine(e.Message) 
			ensure:
				_conn.Close()
		
		outputFileName = Path.Combine(_outputRoot, "SqlUpdate${Revision}.sql")
		
		using writer = System.IO.StreamWriter(outputFileName):
			_databaseModel.UseBuiltins = iif( (_properties["usebuiltins"] and _properties["usebuiltins"].Equals("true", StringComparison.InvariantCultureIgnoreCase)), true, false)
			_databaseModel.GenAdmin = iif( (_properties["genadmin"] and _properties["genadmin"].Equals("true", StringComparison.InvariantCultureIgnoreCase)), true, false)
			_databaseModel.ProjectName = _projectName
			Generate(_templatesFolder, _template, writer, ContextItem("DatabaseModel", _databaseModel) )
			_createdFiles.Add(outputFileName)
			writer.Close()
		
	private def ValidateTables(tables as DataTable):
		for entity as Entity in _model.Entities:
			if tables.Select("Name = '${entity.TableName}'").Length == 0:
				//Need to create table
				newTable = SqlTable(entity, ActionType.ADD)
				newTable.AddComment("The model specifies a table '${entity.TableName}' that does not exist in the database.")
				_databaseModel.AddTable(newTable)
				
		for dr as DataRow in tables.Rows:
			obj = dr["Name"].ToString()
			try:
				_model.GetEntityForTable(obj)
			except e as NullReferenceException:
				// db table does not exist in the model so write delete
				DropTableAndConstraints(obj)
				
	private def ValidateColumns(cols as DataTable, entity as Entity):
		if not List(_databaseModel.Tables).Contains({e as SqlTable | return e.TableName.Equals(entity.TableName, StringComparison.InvariantCultureIgnoreCase) and e.Action == ActionType.ADD}):
			for attribute as EntityAttribute in entity.Attributes:
				dataRows = cols.Select("COLUMN_NAME = '${attribute.ColumnName}'")
				if dataRows.Length == 0:
					//need to add column
					table = _databaseModel.GetTable(entity.TableName)
					if not table:
						table = SqlTable(entity, ActionType.NONE)
						_databaseModel.AddTable(table)
					column = SqlColumn(attribute, ActionType.ADD)
					column.AddComment("The Entity '${entity.ClassName}' contains a column '${attribute.ColumnName}' that is not defined within the table '${entity.TableName}'.")
					table.AddColumn(column)
				else:
					column = SqlColumn(attribute)
					needToAlter = false
					dr = dataRows[0]
					obj = _converter.Convert(dr["DATA_TYPE"].ToString())
					if not attribute.ColumnType.Equals(obj, StringComparison.InvariantCultureIgnoreCase):
						column.AddComment("Model ColumnType is: ${attribute.ColumnType}, Database DataType is: ${obj}.")
						needToAlter = true
					bc = iif(dr["IS_NULLABLE"].ToString().Equals("no", StringComparison.InvariantCultureIgnoreCase), false, true)
					if not attribute.AllowsNull == bc:
						column.AddComment("Model AllowsNull is: ${attribute.AllowsNull}, Database IS_NULLABLE is: ${bc}.")
						needToAlter = true
					obj = dr["CHARACTER_MAXIMUM_LENGTH"].ToString()
					if attribute.Size:
						if attribute.Size.Equals("max", StringComparison.InvariantCultureIgnoreCase):
							if not obj.Equals('-1'):
								column.AddComment("Model Size is: ${attribute.Size}, Database CHARACTER_MAXIMUM_LENGTH is: ${obj}. 'max' is denoted by a size of -1.")
								needToAlter = true
						else:
							if not attribute.Size.Equals(obj):
								column.AddComment("Model Size is: ${attribute.Size}, Database CHARACTER_MAXIMUM_LENGTH is: ${obj}.")
								needToAlter = true
					bc = iif(dr["IsIdentity"].ToString().Equals("0"), false, true)
					if not attribute.IsIdentityColumn == bc:
						column.AddComment("Model IsIdentityColumn is: ${attribute.IsIdentityColumn}, Database identity attribute is: ${bc}.")
						needToAlter = true
				
					if needToAlter:
						column.Action =  ActionType.ALTER
						table = _databaseModel.GetTable(entity.TableName)
						if not table:
							table = SqlTable(entity, ActionType.NONE)
							_databaseModel.AddTable(table)
						table.AddColumn(column)

		for dr as DataRow in cols.Rows:
			if not entity.HasAttribute(dr["COLUMN_NAME"].ToString()):
				//need to add column
				table = _databaseModel.GetTable(entity.TableName)
				if not table:
					table = SqlTable(entity, ActionType.NONE)
					_databaseModel.AddTable(table)
				col = table.CreateColumn(dr['COLUMN_NAME'].ToString())
				col.AddComment("The Entity '${entity.ClassName}' does not contain a column named ${dr['COLUMN_NAME']}.")
				col.Action = ActionType.DROP
				table.AddColumn(col)			
	
	private def DropTableAndConstraints(table as string):
		tableToDrop = SqlTable()
		tableToDrop.AddComment("The table named '${table}' does not exist in the model.")
		sp = "sp_fkeys"
		cmd = _conn.CreateCommand()
		cmd.CommandType = CommandType.StoredProcedure
		cmd.CommandText = sp
		
		par = SqlParameter("@pktable_name", SqlDbType.VarChar )
		par.Direction = ParameterDirection.Input
		par.Value = table
		cmd.Parameters.Add(par)

		sda = SqlDataAdapter()
		sda.SelectCommand = cmd;

		ds = DataSet()
		sda.Fill(ds, table)
		if ds.Tables.Count == 1:
			for dr as DataRow in ds.Tables[0].Rows:
				rel = SqlConstraint()
				rel.ConstraintName = dr['FK_NAME'].ToString()
				rel.ForeignTable = dr['FKTABLE_NAME'].ToString()
				rel.Action = ActionType.DROP
				tableToDrop.AddConstraint(rel)
		tableToDrop.TableName = table
		tableToDrop.Action = ActionType.DROP
		_databaseModel.AddTable(tableToDrop)

	private def ValidateRelations(entity as Entity):
		//In Model Not in DB
		sp = "sp_fkeys"
		for er as EntityRelationship in entity.Relationships:
			if er.Direction == RelDirection.Parent:
				cmd = _conn.CreateCommand()
				cmd.CommandType = CommandType.StoredProcedure
				cmd.CommandText = sp
			
				par = SqlParameter("@pktable_name", SqlDbType.VarChar )
				par.Direction = ParameterDirection.Input
				par.Value = er.LocalEntity.TableName
				cmd.Parameters.Add(par)
				
				par1 = SqlParameter("@fktable_name", SqlDbType.VarChar )
				par1.Direction = ParameterDirection.Input
				par1.Value = er.ForeignTableName
				cmd.Parameters.Add(par1)

				sda = SqlDataAdapter()
				sda.SelectCommand = cmd;

				ds = DataSet()
				sda.Fill(ds, er.LocalEntity.TableName)
				if ds.Tables.Count == 1:
					if ds.Tables[0].Rows.Count == 0:
						//need to create constraint
						table = _databaseModel.GetTable(entity.TableName)
						if not table:
							table = SqlTable(entity, ActionType.NONE)
							_databaseModel.AddTable(table)
						constraint = SqlConstraint(er)
						constraint.AddComment("Model specifies that ${er.ForeignTableName}.${er.ForeignKey} has a FK CONSTRAINT on ${er.LocalEntity.TableName}.${er.LocalKey} that is not in the DB.")
						constraint.Action = ActionType.ADD
						table.AddConstraint(constraint)
					else:
						print ds.Tables[0].Rows.Count
					
						constraint = SqlConstraint(er)
						needToAlter = false
						
						results = ds.Tables[0].Select(String.Format("PKCOLUMN_NAME = '{0}'", er.LocalKey))
						if (results.Length == 0):
							needToAlter = true
							constraint.AddComment("The local key name is not consistent with the model.")
							
						results = ds.Tables[0].Select(String.Format("FKCOLUMN_NAME = '{0}'", er.ForeignKey))
						if (results.Length == 0):
							needToAlter = true
							constraint.AddComment("The foreign key name is not consistent with the model.")
						
						results = ds.Tables[0].Select(String.Format("PKCOLUMN_NAME = '{0}' and FKCOLUMN_NAME = '{1}'", er.LocalKey, er.ForeignKey))
						if (results.Length > 0):
							dr = results[0]
							obj = GetUpdateAndDeleteAction(dr["UPDATE_RULE"].ToString())
							compObj = er.UpdateRule.ToString()
							if not obj.Equals(compObj, StringComparison.InvariantCultureIgnoreCase):
								needToAlter = true
								constraint.AddComment("The update rule is not consistent with the model.")
							obj = GetUpdateAndDeleteAction(dr["DELETE_RULE"].ToString())
							compObj = er.DeleteRule.ToString()
							if not obj.Equals(compObj, StringComparison.InvariantCultureIgnoreCase):
								needToAlter = true
								constraint.AddComment("The delete rule is not consistent with the model.")
						if needToAlter:
							table = _databaseModel.GetTable(entity.TableName)
							if not table:
								table = SqlTable(entity, ActionType.NONE)
								_databaseModel.AddTable(table)
							constraint.Action = ActionType.ALTER
							table.AddConstraint(constraint)
		
	private def GetUpdateAndDeleteAction(action as string) as string:
		action = action.ToLower()
		if action.Equals("0"):
			return "cascade"
		if action.Equals("1"):
			return "none"
		if action.Equals("2"):
			return "setnull"
		if action.Equals("no action"):
			return "none"
		if action.Equals("cascade"):
			return "cascade"
		return "none"

	
	protected def GenerateOutputFromTemplate(outputFile as string, templateName as string, ctx as GenerationContext, forceForEntity as bool):
		if File.Exists(Path.Combine(_templatesFolder, templateName)) == false:
			raise System.IO.FileNotFoundException("Template file not found: '${templateName}'", templateName)
		if _forceOverwrite or File.Exists(outputFile) == false or forceForEntity:
			Console.WriteLine("Writing ${outputFile}")
			using writer = StreamWriter(outputFile):
				Generate(templateName, ctx, writer)

	protected def Generate(templateName as string, context as GenerationContext, writer as TextWriter):
		ve = VelocityEngine()
		ep = ExtendedProperties()
		ep.SetProperty(RuntimeConstants.FILE_RESOURCE_LOADER_PATH, _templatesFolder)
		ep.SetProperty(RuntimeConstants.RUNTIME_LOG_ERROR_STACKTRACE, "true")
		ep.SetProperty(RuntimeConstants.RUNTIME_LOG_WARN_STACKTRACE, "true")
		ep.SetProperty(RuntimeConstants.RUNTIME_LOG_INFO_STACKTRACE, "true")
		ep.SetProperty(RuntimeConstants.UBERSPECT_CLASSNAME, "NVelocity.Util.Introspection.UberspectImpl")
		ep.SetProperty("directive.manager", "NVelocity.Runtime.Directive.DirectiveManager; NVelocity")
		ve.Init(ep)
		template = ve.GetTemplate(templateName)
		velocityContext = NVelocity.VelocityContext()
		velocityContext.Put('Rails', context)
		velocityContext.Put('Neo', context)
		velocityContext.Put('ThisTemplateName', templateName)
		template.Merge(velocityContext, writer)
		
	protected def Generate(templateName as string, writer as TextWriter, *contextItems as (ContextItem)):
		ve = VelocityEngine()
		ep = ExtendedProperties()
		ep.SetProperty(RuntimeConstants.FILE_RESOURCE_LOADER_PATH, _templatesFolder)
		ep.SetProperty(RuntimeConstants.RUNTIME_LOG_ERROR_STACKTRACE, "true")
		ep.SetProperty(RuntimeConstants.RUNTIME_LOG_WARN_STACKTRACE, "true")
		ep.SetProperty(RuntimeConstants.RUNTIME_LOG_INFO_STACKTRACE, "true")
		ep.SetProperty(RuntimeConstants.UBERSPECT_CLASSNAME, "NVelocity.Util.Introspection.UberspectImpl")
		ep.SetProperty("directive.manager", "NVelocity.Runtime.Directive.DirectiveManager; NVelocity")
		ve.Init(ep)
		template = ve.GetTemplate(templateName)
		velocityContext = NVelocity.VelocityContext()
		for contextitem as ContextItem in contextItems:
			velocityContext.Put(contextitem.Name, contextitem.Item)
		velocityContext.Put('ThisTemplateName', templateName)
		template.Merge(velocityContext, writer)

	protected def GenerateOutputFromTemplate(outputFile as string, templatePath as string, templateName as string, ctx as GenerationContext):
		if File.Exists(Path.Combine(templatePath, templateName)) == false:
			raise System.IO.FileNotFoundException("Template file not found: '${templateName}'", templateName)
		if _forceOverwrite or (File.Exists(outputFile) == false):
			Console.WriteLine("Writing ${outputFile}")
			using writer = StreamWriter(outputFile):
				Generate(templatePath, templateName, ctx, writer)

	protected def Generate(templatePath as string, templateName as string, context as GenerationContext, writer as TextWriter):
		ve = VelocityEngine()
		ep = ExtendedProperties()
		ep.SetProperty(RuntimeConstants.FILE_RESOURCE_LOADER_PATH, templatePath)
		ep.SetProperty(RuntimeConstants.RUNTIME_LOG_ERROR_STACKTRACE, "true")
		ep.SetProperty(RuntimeConstants.RUNTIME_LOG_WARN_STACKTRACE, "true")
		ep.SetProperty(RuntimeConstants.RUNTIME_LOG_INFO_STACKTRACE, "true")
		ep.SetProperty(RuntimeConstants.UBERSPECT_CLASSNAME, "NVelocity.Util.Introspection.UberspectImpl")
		ep.SetProperty("directive.manager", "NVelocity.Runtime.Directive.DirectiveManager; NVelocity")
		ve.Init(ep)
		template = ve.GetTemplate(templateName)
		velocityContext = NVelocity.VelocityContext()
		velocityContext.Put('Rails', context)
		velocityContext.Put('Neo', context)
		velocityContext.Put('ThisTemplateName', templateName)
		template.Merge(velocityContext, writer)
	
	protected def Generate(templatePath as string, templateName as string, writer as TextWriter, *contextItems as (ContextItem)):
		ve = VelocityEngine()
		ep = ExtendedProperties()
		ep.SetProperty(RuntimeConstants.FILE_RESOURCE_LOADER_PATH, templatePath)
		ep.SetProperty(RuntimeConstants.RUNTIME_LOG_ERROR_STACKTRACE, "true")
		ep.SetProperty(RuntimeConstants.RUNTIME_LOG_WARN_STACKTRACE, "true")
		ep.SetProperty(RuntimeConstants.RUNTIME_LOG_INFO_STACKTRACE, "true")
		ep.SetProperty(RuntimeConstants.UBERSPECT_CLASSNAME, "NVelocity.Util.Introspection.UberspectImpl")
		ep.SetProperty("directive.manager", "NVelocity.Runtime.Directive.DirectiveManager; NVelocity")
		ve.Init(ep)
		template = ve.GetTemplate(templateName)
		velocityContext = NVelocity.VelocityContext()
		for contextitem as ContextItem in contextItems:
			velocityContext.Put(contextitem.Name, contextitem.Item)
		velocityContext.Put('ThisTemplateName', templateName)
		template.Merge(velocityContext, writer)
	
	protected def iif(condition as bool, trueValue, falseValue):
		if condition:
			return trueValue
		return falseValue
